package cn.yunhe.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.yunhe.entity.University;
import cn.yunhe.util.DButil;

public class UniverstityDaoImpl implements IUniversityDao {

	static Connection conn = null;
	static PreparedStatement ps = null;
	static ResultSet rs = null;

	public List<University> searchIsnef() {
		conn = DButil.getCon();
		List<University> list = new ArrayList<University>();
		try {
			String sql = "select Universityid,uname,isnef,istoo,contacts,url from UNIVERSITYS where isnef=1";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				University university = new University();
				university.setUniversityid(rs.getInt("universityid"));
				university.setUname(rs.getString("uname"));
				university.setIsnef(rs.getInt("isnef"));
				university.setIstoo(rs.getInt("istoo"));
				university.setContacts(rs.getString("contacts"));
				university.setUrl(rs.getString("url"));
				list.add(university);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;

	}
	// ͨ�ò�ѯ�б�

	public static List<University> search(String sql, Object[] pramas) {
		List<University> list = new ArrayList<University>();
		conn = DButil.getCon();
		try {
			ps = conn.prepareStatement(sql);
			DButil.setPramas(ps, pramas);
			rs = ps.executeQuery();
			while (rs.next()) {
				University university = new University();
				university.setUniversityid(rs.getInt("universityid"));
				university.setUname(rs.getString("uname"));
				university.setIsnef(rs.getInt("isnef"));
				university.setIstoo(rs.getInt("istoo"));
				university.setContacts(rs.getString("contacts"));
				university.setUrl(rs.getString("url"));
				list.add(university);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
		} finally {
			DButil.close(conn, ps, rs);
		}
		return list;
	}

	/**
	 * 
	 * ��ȡȫ����Ϣ
	 * 
	 */
	public List<University> getAll() {


		String sql = "select * from UNIVERSITYS";
		return search(sql,null);

	}

	public List<University> searchIstoo() {
		conn = DButil.getCon();
		List<University> list = new ArrayList<University>();
		try {
			String sql = "select Universityid,uname,isnef,istoo,contacts,url from UNIVERSITYS where istoo=1";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				University university = new University();
				university.setUniversityid(rs.getInt("universityid"));
				university.setUname(rs.getString("uname"));
				university.setIsnef(rs.getInt("isnef"));
				university.setIstoo(rs.getInt("istoo"));
				university.setContacts(rs.getString("contacts"));
				university.setUrl(rs.getString("url"));
				list.add(university);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	public List<University> searchCommon() {

		conn = DButil.getCon();
		List<University> list = new ArrayList<University>();
		try {
			String sql = "select universityid,uname,isnef,istoo,contacts,url from UNIVERSITYS where istoo=0 and isnef=0";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				University university = new University();
				university.setUniversityid(rs.getInt("universityid"));
				university.setUname(rs.getString("uname"));
				university.setIsnef(rs.getInt("isnef"));
				university.setIstoo(rs.getInt("istoo"));
				university.setContacts(rs.getString("contacts"));
				university.setUrl(rs.getString("url"));
				list.add(university);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}


	public University selectUniById(int universityid) {

		

		String sql = "select * from UNIVERSITYS where universityid=?";
		Object[] pramas = {universityid};
		List<University> list = search(sql,pramas);

		return list.get(0);
	}


	
	public int addUniversity(University university) {
		String sql = "insert into universitys values(seq_sys_university.nextval,?,?,?,?,?)";
		Object[] pramas = { university.getUname(), university.getIsnef(), university.getIstoo(),
				university.getContacts(), university.getUrl() };
		return DButil.insertOrDelOrUpdate(sql, pramas);

	}

	public int deleteUniById(int universityid) {
		String sql = "delete from  universitys where universityid=?";
		Object[] pramas = { universityid };
		return DButil.insertOrDelOrUpdate(sql, pramas);
	}

	public int updateUniById(University university) {
		String sql = "update universitys set uname=?,isnef=?,istoo=?,contacts=?,url=?  where universityid=?";
		Object[] pramas = { university.getUname(), university.getIsnef(), university.getIstoo(),
				university.getContacts(), university.getUrl(), university.getUniversityid() };
		return DButil.insertOrDelOrUpdate(sql, pramas);

	}

	@Override
	public int add(University u) {
		String sql ="insert into UNIVERSITYS values(seq_sys_majors.nextval,?,?,?,?,?)";
		Object[] pramas = {u.getUname(),u.getIsnef(),u.getIstoo(),u.getContacts(),u.getUrl()};
		return DButil.insertOrDelOrUpdate(sql, pramas);
	}

	@Override
	public int updateUniversity(University u) {
		// TODO Auto-generated method stub
		String sql = "update UNIVERSITYS set uname=?,isnef=?,isnoo=?,contacts=?,url=?where universityid=?";
		Object[] pramas={u.getUname(),u.getIsnef(),u.getIstoo(),u.getContacts(),u.getUniversityid()};
		return DButil.insertOrDelOrUpdate(sql, pramas);
	}

	@Override
	public int del(int universityid) {
		String sql = "delete from UNIVERSITYS where universityid=?";
		Object[] pramas = {universityid};
		return DButil.insertOrDelOrUpdate(sql, pramas);
	}


}
